Though I have been using SQL Server, Oracle from last decade, I have been asked to
do some research on PostgreSQL and after some initial investigation it is evident that I am now stuck on retrieving data from the PostgreSQL database using Function.
Using following piece of code to retrieve the data and getting error
('ERROR [26000] ERROR: prepared statement "mytabletest" does not exist;
'Error while executing the query)
Code Snippets
Dim oDBCommand As DbCommand = GetDBCommand(oConnectionType, "mytabletest", CommandType.StoredProcedure)
Dim dstResults As DataSet = GetDataSet(ConnectionTypes.ODBC, oDBCommand)
Public Function GetDataReader(dbType As ConnectionTypes, command As DbCommand) As DbDataReader
Try
Dim oConnection As DbConnection = GetDBConnection(dbType)
Dim oDBTransaction As DbTransaction = oConnection.BeginTransaction
command.Connection = oConnection
command.Transaction = oDBTransaction
'GETTING ERROR ON FOLLOWING LINE
'ERROR [26000] ERROR: prepared statement "mytabletest" does not exist;
'Error while executing the query
return command.ExecuteReader()
Catch ex As Exception
Throw ex
Finally
End Try
Return Nothing
End Function
Environement I am currently working on is following:-
32 Bit Machine.
Visual Studio 2010 + SP1
ODBC Prodiver: PostgreSQL Unicode 9.01.02.00
ADO.Net (System.Data.Odbc)
Please note that I am open to any suggestions i.e. if I am completely doing it wrong
OR partially etc. Please feel free to write.
In order to make it easier for you to create a same environment, please use following table/function definition.
--- Simple table to make things easier to understand. <br>
CREATE TABLE mytable
(
messagetypeid integer NOT NULL,
messagetype character varying(100) NOT NULL
)
-- Function to retrieve data. <br>
CREATE OR REPLACE FUNCTION mytabletest() <br>
RETURNS SETOF refcursor AS $$
DECLARE
ref1 refcursor;
BEGIN
OPEN ref1 FOR SELECT * FROM mytable;
RETURN NEXT ref1;
END;
$$ LANGUAGE plpgsql;
Please Note:
If I use <br>
Dim oDBCommand As DbCommand = GetDBCommand(oConnectionType, "SELECT * FROM mytable", CommandType.Text)
then system manages to retrieve information from the datbase without any issue, however, as I mentioned as soon we use "Function" it throws an exception.
During my failed efforts to search any solution from the internet someone mentioned that Table should be created with the lower case it so just for the sake of it I recreated with the lower case, however, problem persists.
Copyright Notice：Content Author:「Asfand Iqbal」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/15968999/retrieve-data-from-postgresql-database-using-ado-net-system-data-odbc-vb-net